package main;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.TimerTask;

import util.DBUtils;

public class Quartz extends TimerTask{

	
	private final static String from_driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private final static String from_url="jdbc:sqlserver://192.168.2.254:1433;databaseName=JSOCTNet";
	private final static String from_user="sa";
	private final static String from_pwd="ql56_nn";
	
	private final static String to_driver="com.mysql.jdbc.Driver";
	private final static String to_url="jdbc:mysql://127.0.0.1:3306/csch?characterEncoding=utf8";
	private final static String to_user="root";
	private final static String to_pwd="123456789";
	
	@Override
	public void run() {
		System.err.println("...............启用线程，开始同步.............");
		class SyncRecords extends Thread {

			@Override
			public void run() {
				try {
					Long _startTime = System.currentTimeMillis();
					System.out.println("-----------------------------------...开始执行IC卡信息同步");
					int count = insertDataByBatch();
					System.out.println("/-|-\\|/-|-\\|/-|-\\|/-|-\\|/-|-\\|...耗时"+(System.currentTimeMillis()-_startTime)/1000+"秒，更新记录"+count+"条，已完成IC卡信息同步");
					
				} catch (Exception e) {
					e.printStackTrace();
					System.err.println(e.getMessage());
				}
			}
			
			private ArrayList<Map> getAllData() throws Exception {
				String sql = "select identify_field from portal_synch_task where task_id = -99998";
				
				String lastExecuteId = "";
				Connection conn = null;
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				try{
					conn = DBUtils.getInitJDBCUtil().getConnection(Quartz.to_driver,Quartz.to_url,Quartz.to_user,Quartz.to_pwd);
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if(rs.next()){
						lastExecuteId = rs.getString(1);
					}
				}catch(Exception e){
					System.err.println(e.getMessage());
				}finally{
					DBUtils.getInitJDBCUtil().closeConnection(rs, pstmt, conn);
				}
				
				ArrayList<Map> list = new ArrayList<Map>();
				
				StringBuffer buffer = new StringBuffer();
				buffer.append("select a.IssueID as id, a.CardB2PName as busUnitId,a.IDNO as IDNo,a.PersonNO as MemberCode,convert(nvarchar(20),a.OptDate,120) as createDate,a.Status as icStatus ,b.Balance from MC.View_CardInfo_Sync a , SF.View_CardInfoJS520 b where a.IDNO = b.IDNO and a.Status = 1 ");
				if(lastExecuteId!=null & lastExecuteId!=""){
					buffer.append(" and a.IssueID > "+lastExecuteId );
				}
				
				try{
					conn = DBUtils.getInitJDBCUtil().getConnection(Quartz.from_driver,Quartz.from_url,Quartz.from_user,Quartz.from_pwd);
					pstmt = conn.prepareStatement(buffer.toString());
					rs = pstmt.executeQuery();
					while(rs.next()){
						Map eb = new HashMap();
						eb.put("ID",rs.getString(1));
						eb.put("busUnitId",rs.getString(2));
						eb.put("idNo",rs.getString(3));
						eb.put("memberCode",rs.getString(4));
						eb.put("createDate",rs.getString(5));
						eb.put("icStatus",rs.getString(6));
						eb.put("cardBalance",rs.getString(7));
						
						list.add(eb);
					}
				}catch(Exception e){
					System.err.println(e.getMessage());
				}finally{
					DBUtils.getInitJDBCUtil().closeConnection(rs, pstmt, conn);
				}
				
				return list;
			}
			
			private int insertDataByBatch() throws Exception {
				int count = 0;
				ArrayList<Map> list = getAllData();
				if (list.size() <= 0)
					return count;

				Connection conn = DBUtils.getInitJDBCUtil().getConnection(Quartz.to_driver,Quartz.to_url,Quartz.to_user,Quartz.to_pwd);
				PreparedStatement pstmt = null;
				ResultSet rs = null;

				for (Map eb : list) {
					
					if(eb.get("idNo") == null){
						continue;
					}
					
					StringBuffer tempBuffer = new StringBuffer();
					tempBuffer.append("insert into account_card_info (ic_card_no, card_cat_id,member_code,status,open_card_date,modified_date,is_active,bus_unit_id,card_balance) values ");
					
					tempBuffer.append("(");
					
					if (eb.get("idNo") == null) {
						tempBuffer.append("null");
					} else {
						tempBuffer.append("'" + eb.get("idNo") + "'");
					}
					
					tempBuffer.append(",");
					tempBuffer.append("'3'");
					
					tempBuffer.append(",");
					if (eb.get("memberCode") == null) {
						tempBuffer.append("null");
						tempBuffer.append(",'0'");
					} else {
						tempBuffer.append("'" + eb.get("memberCode") + "'");
						tempBuffer.append(",'1'");
					}
					
					tempBuffer.append(",");
					if (eb.get("createDate") == null) {
						tempBuffer.append("null");
						tempBuffer.append(",null");
					} else {
						tempBuffer.append("'" + eb.get("createDate") + "'");
						tempBuffer.append(",'" + eb.get("createDate") + "'");
					}
					
					tempBuffer.append(",");
					if (eb.get("icStatus") == null) {
						tempBuffer.append("null");
					} else {
						tempBuffer.append("'" + eb.get("icStatus") + "'");
					}
					
					tempBuffer.append(",");
					if (eb.get("busUnitId") == null) {
						if(eb.get("busUnitId").toString().indexOf("南宁")>=0){
							tempBuffer.append("'1'");
						}
					} else {
						tempBuffer.append("'1'");
					}
					
					tempBuffer.append(",");
					if (eb.get("cardBalance") == null) {
						tempBuffer.append("null");
					} else {
						tempBuffer.append("'" + eb.get("cardBalance") + "'");
					}
					
					tempBuffer.append(")");
					

					pstmt = conn.prepareStatement(tempBuffer.toString());
					count = pstmt.executeUpdate();
				}
				
				Map eb = list.get(list.size() - 1);
				String lastValue = (String) eb.get("ID");
				if(lastValue!=null & lastValue!=""){
					String sql = "update portal_synch_task set identify_field = '"+lastValue+"' where task_id = -99998";
					try{
						pstmt = conn.prepareStatement(sql);
						pstmt.executeUpdate();
					}catch(Exception e){
						System.err.println(e.getMessage());
					}finally{
						DBUtils.getInitJDBCUtil().closeConnection(rs, pstmt, conn);
					}
				}
				return count;
			}
			
		}
		
		class SyncCards extends Thread {

			@Override
			public void run() {
				try {
					System.out.println("-----------------------------------...开始执行消费信息同步");
					Long _startTime = System.currentTimeMillis();
					int count = insertDataByBatch();
					System.out.println("/-|-\\|/-|-\\|/-|-\\|/-|-\\|/-|-\\|...耗时"+(System.currentTimeMillis()-_startTime)/1000+"秒，更新记录"+count+"条，已完成消费信息同步");
				} catch (Exception e) {
					e.printStackTrace();
					System.err.println(e.getMessage());
				}
			}
				private ArrayList<Map> getAllData() throws Exception {
					String sql = "select identify_field from portal_synch_task where task_id = -99999";

					Connection conn = null;
					PreparedStatement pstmt = null;
					ResultSet rs = null;
					String lastExecuteId = "";
					
					try{
						conn = DBUtils.getInitJDBCUtil().getConnection(Quartz.to_driver,Quartz.to_url,Quartz.to_user,Quartz.to_pwd);
						pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						if(rs.next()){
							lastExecuteId = rs.getString(1);
						}
					}catch(Exception e){
						System.err.println(e.getMessage());
					}finally{
						DBUtils.getInitJDBCUtil().closeConnection(rs, pstmt, conn);
					}
					
					ArrayList<Map> list = new ArrayList<Map>();
					
					StringBuffer buffer = new StringBuffer();
					buffer.append("select ID as id , DeviceB2PName as busUnitId , IDNO as icNo ,DeviceName as expenseType ,BrushCardTime as createTime , (Charge4+Balance) as expenseInit ,Charge4 as expenseChange ,Balance as expenseBalance,PersonNO as memberCode from SF.View_Record_Sync");
					if(lastExecuteId!=null & lastExecuteId!=""){
						buffer.append(" where ID > "+lastExecuteId );
					}
					
					try{
						conn = DBUtils.getInitJDBCUtil().getConnection(Quartz.from_driver,Quartz.from_url,Quartz.from_user,Quartz.from_pwd);
						pstmt = conn.prepareStatement(buffer.toString());
						rs = pstmt.executeQuery();
						while(rs.next()){
							Map eb = new HashMap();
							eb.put("ID",rs.getString(1));
							eb.put("busUnitId",rs.getString(2));
							eb.put("icNo",rs.getString(3));
							eb.put("expenseType",rs.getString(4));
							eb.put("createTime",rs.getString(5));
							eb.put("expenseInit",rs.getString(6));
							eb.put("expenseChange",rs.getString(7));
							eb.put("expenseBalance",rs.getString(8));
							eb.put("memberCode",rs.getString(9));
							list.add(eb);
						}
					}catch(Exception e){
						System.err.println(e.getMessage());
					}finally{
						DBUtils.getInitJDBCUtil().closeConnection(rs, pstmt, conn);
					}
					
					return list;
				}
				
				private int insertDataByBatch() throws Exception {
					int count = 0;
					ArrayList<Map> list = getAllData();
					if (list.size() <= 0)
						return count;

					Connection conn = DBUtils.getInitJDBCUtil().getConnection(Quartz.to_driver,Quartz.to_url,Quartz.to_user,Quartz.to_pwd);
					PreparedStatement pstmt = null;
					ResultSet rs = null;
					
					for (Map eb : list) {
						
						if(eb.get("memberCode") == null){
							continue;
						}
						StringBuffer tempBuffer = new StringBuffer();
						tempBuffer.append("insert into  account_expense_history (member_code,ic_card_no,expense_type,expense_init,expense_change,expense_balance,create_datetime,bus_unit_id) values ");
						
						tempBuffer.append("(");
						
						if (eb.get("memberCode") == null) {
							tempBuffer.append("null");
						} else {
							tempBuffer.append("'" + eb.get("memberCode") + "'");
						}
						
						tempBuffer.append(",");
						if (eb.get("icNo") == null) {
							tempBuffer.append("null");
						} else {
							tempBuffer.append("'" + eb.get("icNo") + "'");
						}
						
						tempBuffer.append(",");
						if (eb.get("expenseType") != null) {
							
							if(eb.get("expenseType").toString().indexOf("交易大厅")>=0){
								tempBuffer.append("'1'");
							}else if(eb.get("expenseType").toString().indexOf("餐厅")>=0){
								tempBuffer.append("'2'");
							}else if(eb.get("expenseType").toString().indexOf("娱乐")>=0){
								tempBuffer.append("'3'");
							}else if(eb.get("expenseType").toString().indexOf("停车")>=0){
								tempBuffer.append("'4'");
							}
						} else {
							tempBuffer.append("'4'");
						}
						
						tempBuffer.append(",");
						if (eb.get("expenseInit") == null) {
							tempBuffer.append("null");
						} else {
							tempBuffer.append("'" + eb.get("expenseInit") + "'");
						}
						
						tempBuffer.append(",");
						if (eb.get("expenseChange") == null) {
							tempBuffer.append("null");
						} else {
							tempBuffer.append("'" + eb.get("expenseChange") + "'");
						}
						
						tempBuffer.append(",");
						if (eb.get("expenseBalance") == null) {
							tempBuffer.append("null");
						} else {
							tempBuffer.append("'" + eb.get("expenseBalance") + "'");
						}
						
						tempBuffer.append(",");
						if (eb.get("createTime") == null) {
							tempBuffer.append("null");
						} else {
							tempBuffer.append("'" + eb.get("createTime") + "'");
						}
						
						tempBuffer.append(",");
						if (eb.get("busUnitId") == null) {
							if(eb.get("busUnitId").toString().indexOf("南宁")>=0){
								tempBuffer.append("'1'");
							}
						} else {
							tempBuffer.append("'1'");
						}
						
						tempBuffer.append(")");
						
						pstmt = conn.prepareStatement(tempBuffer.toString());
						count = pstmt.executeUpdate();
					}
					
					Map eb = list.get(list.size() - 1);
					String lastValue = (String) eb.get("ID");
					if(lastValue!=null & lastValue!=""){
						
						String sql = "update portal_synch_task set identify_field = '"+lastValue+"' where task_id = -99999";

						try{
							pstmt = conn.prepareStatement(sql);
							pstmt.executeUpdate();
						}catch(Exception e){
							System.err.println(e.getMessage());
						}finally{
							DBUtils.getInitJDBCUtil().closeConnection(rs, pstmt, conn);
						}
					}
					
					for(Map eb1 : list){
						if(eb1.get("memberCode") != null){
							Long EXPENSE_TYPE = 4L;

							if(eb1.get("expenseType").toString().indexOf("交易大厅")>=0){
								EXPENSE_TYPE = 1L;
							}else if(eb1.get("expenseType").toString().indexOf("餐厅")>=0){
								EXPENSE_TYPE = 2L;
							}else if(eb1.get("expenseType").toString().indexOf("娱乐")>=0){
								EXPENSE_TYPE = 3L;
							}else if(eb1.get("expenseType").toString().indexOf("停车")>=0){
								EXPENSE_TYPE = 4L;
							}
							
							/*if(EXPENSE_TYPE!=1L){
								AccountExpenseHistoryService.getInstance().CalculateMemberScore(new BigDecimal(eb1.get("expenseChange").toString()), Const.EXPENSE_TYPE_CANYIN,eb1.get("memberCode").toString());					
							}*/
						}
					}
					return count;
				}
			
		}

		SyncRecords method1 = new SyncRecords();
		method1.start();
		SyncCards method2 = new SyncCards();
		method2.start();
	}

}
